
--EXEC SP_CREATE_UPDATE_NONVALUE_INVENTORY_LOT 13358, 435, '04/09/2009', '1;2;3', 'MANULLAY INSERT', 'Admin'

CREATE PROC SP_CREATE_UPDATE_NONVALUE_INVENTORY_LOT
(
	@LOT_ID		INT,
	@QTY_IN		INT,
	@DATE_IN    DATETIME,
	@WAFERIDS	VARCHAR(300),
	@COMMENTS 	VARCHAR(500),
	@USER_NAME	VARCHAR(50)
)
AS
BEGIN
	
	DECLARE @PRV_WAFERIDS 		VARCHAR(300)
	DECLARE @PRV_WAFERIDS_AS_COMMA	 VARCHAR(300)
    DECLARE @PRESENT_WAFERIDS_AS_COMMA	 VARCHAR(300)
	DECLARE @PRV_WAFER_IDS_TABLE 	TABLE ( WAFER_ID INT )
	DECLARE	@PRESENT_WAFER_IDS_TABLE    TABLE ( WAFER_ID INT )
	DECLARE @WAFER_IDS_TABLE 		TABLE ( WAFER_ID INT )
	DECLARE @CONCAT_WAFERS_INTO_STRING 	VARCHAR(300)
	DECLARE @LOT_STATUS VARCHAR(50)

	IF @QTY_IN > 0  
	BEGIN
		IF( ( SELECT COUNT(*) FROM NONVALUE_WIP_TABLE WHERE LOT_ID = @LOT_ID AND [TIMESTAMP] = CONVERT( VARCHAR, GETDATE(), 101 ) ) = 0 ) 
		BEGIN
			INSERT INTO NONVALUE_WIP_TABLE ( LOT_ID, QTY_IN, WAFER_IDS, UPDATED_BY,  [TIMESTAMP], COMMENTS )
			VALUES ( @LOT_ID, @QTY_IN, @WAFERIDS, @USER_NAME, CURRENT_TIMESTAMP, @COMMENTS )
		END
		ELSE
		BEGIN
			/* Partial Scrapping for One wafer is useful*/
			SELECT @PRV_WAFERIDS = WAFER_IDS FROM NONVALUE_WIP_TABLE WHERE LOT_ID = @LOT_ID AND [TIMESTAMP] = CONVERT( VARCHAR, GETDATE(), 101 )

			SET @PRV_WAFERIDS_AS_COMMA = REPLACE(@PRV_WAFERIDS, ';',',')
			SET @PRESENT_WAFERIDS_AS_COMMA = REPLACE(@WAFERIDS, ';',',')

			INSERT INTO @PRV_WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @PRV_WAFERIDS_AS_COMMA   )		
			INSERT INTO @PRESENT_WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @PRESENT_WAFERIDS_AS_COMMA  )		
			
			INSERT INTO @WAFER_IDS_TABLE SELECT WAFER_ID FROM @PRV_WAFER_IDS_TABLE UNION SELECT WAFER_ID FROM @PRESENT_WAFER_IDS_TABLE

			SET @CONCAT_WAFERS_INTO_STRING = NULL
	
			SELECT @CONCAT_WAFERS_INTO_STRING = COALESCE( @CONCAT_WAFERS_INTO_STRING + ';' + CONVERT( VARCHAR(2), WAFER_ID ), CONVERT( VARCHAR(2) , WAFER_ID ) )
					FROM @WAFER_IDS_TABLE ORDER BY WAFER_ID 
			/* END Partial Scrapping */

			UPDATE NONVALUE_WIP_TABLE SET QTY_IN 	= ISNULL( QTY_IN, 0 )+ @QTY_IN, 
						 WAFER_IDS 	= @CONCAT_WAFERS_INTO_STRING, 
						 UPDATED_BY	= @USER_NAME,
						 [TIMESTAMP]= CURRENT_TIMESTAMP,
						 COMMENTS   = @COMMENTS
			WHERE LOT_ID = @LOT_ID AND [TIMESTAMP] = CONVERT( VARCHAR, GETDATE(), 101 )
		END		
		
		UPDATE	SCRAPPED_LOTS SET SL_SCRAPPED_DIE_QTY = SL_SCRAPPED_DIE_QTY - @QTY_IN WHERE LOT_ID = @LOT_ID AND SL_SCRAPPED_DATE = CONVERT( VARCHAR, @DATE_IN, 101 )

		SELECT @LOT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
		IF @LOT_STATUS = 'Close'
		BEGIN
				UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Running', QTY_IN = @QTY_IN WHERE LOT_ID = @LOT_ID
		END
		ELSE
		BEGIN
			 UPDATE LOT_TABLE SET QTY_IN = QTY_IN + @QTY_IN WHERE LOT_ID = @LOT_ID	
		END
	END
END